# -*_ codeing=utf-8 -*-
# @Time: 2025/10/27 20:24
# @Author: foxhuty
# @File: query_data.py
# @Software: PyCharm
# @Based on python 3.10
from sqlalchemy import select, func, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy_notes.db_main import engine
from sqlalchemy_notes.student_manager.models import Student

with sessionmaker(bind=engine).begin() as session:
    # get返回一条数据
    data_searched = session.get(Student, 4)
    print(data_searched)
    # 返回全部数据
    data_all = select(Student)
    result = session.scalars(data_all).fetchall()
    for student in result:
        print(student.name)
    # 另一种返回全部数据
    sql = text('select * from tb_students')
    data_read = session.execute(sql).all()
    for obj in data_read:
        print(obj.student_name, obj.salary, obj.bonus, obj.gender)

    # 第一种过滤条件
    # stmt=select(Student).where(Student.name == '高市早苗')
    stmt = select(Student).where(Student.name.is_(None))
    result = session.execute(stmt).scalars()
    for obj in result:
        print(obj)
    # 第二种过滤条件
    stmt2 = select(Student).where(Student.name != '高市早苗')
    # stmt2=select(Student).where(Student.name.isnot(None))
    stmt3 = select(Student).where(Student.name.like("%本%"))
    stmt4 = select(Student).where(Student.id.in_([2, 5]))
    stmt5 = select(Student).where(Student.name.like('%本%'), Student.salary >= 2000)
    result2 = session.execute(stmt5).scalars()
    for obj in result2:
        print(obj)
    # 聚合函数
    stmt_total=select(func.avg(Student.bonus))
    stmt_count=select(func.count(Student.bonus))
    result_count=session.execute(stmt_count).first()
    print(result_count)
    # 分页
    result_page=session.execute(select(Student).offset(5).limit(3)).scalars()
    for obj in result_page:
        print(obj)
    #排序
    result_order=session.execute(select(Student).order_by(Student.salary.desc())).scalars()
    for obj in result_order:
        print(obj)
    # 分组group_by
    stmt_group=select(Student.gender,func.count(Student.id)).group_by(Student.gender)
    result_group=session.execute(stmt_group).all()
    for obj in result_group:
        print(obj.gender.value,obj.count)






